MySQL Update or Rename a Key in JSON

Ask Time：2018-11-22T22:39:51 Author：ltdev

I'm having this json stored in db

{
    "endDate": "2018-10-10",
    "startDate": "2017-09-05", 
    "oldKeyValue": {
        "foo": 1000, 
        "bar": 2000, 
        "baz": 3000
    },
    "anotherValue": 0
}

How can I rename "oldKeyValue" key to "newKeyValue" without knowing the index of the key in an UPDATE query? I'm looking for something like this

UPDATE `my_table` SET `my_col` = JSON()

NOTE: only the key needs to change, the values (i.e. {"foo": 1000, "bar": 2000, "baz": 3000}) should remain the same

Author:ltdev，eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/53433285/mysql-update-or-rename-a-key-in-json

Peter VARGA :

I personally prefer another method:\n\nUPDATE my_table SET my_col = REPLACE(my_col, '\"oldKeyValue\":', '\"newKeyValue\":')\n\n\nThis replaces directly the key name in the JSON string without destroying the JSON structure.\n\nI am using the additional : in order to avoid an unintentional replacement in a value.",

2019-01-17T17:53:23

Madhur Bhaiya :

There is no straightforward JSON function to do the same. We can use a combination of some JSON functions.\n\nWe will remove the oldKey-oldValue pair using Json_Remove() function, and then Json_Insert() the newKey-oldValue pair.\n\nJson_Extract() function is used to fetch value corresponding to an input key in the JSON document.\n\nUPDATE `my_table` \nSET `my_col` = JSON_INSERT(\n JSON_REMOVE(my_col, '$.oldKeyValue'), \n '$.newKeyValue', \n JSON_EXTRACT(my_col, '$.oldKeyValue')\n );\n\n\nDemo\n\nSET @my_col := '{\"endDate\": \"2018-10-10\", \"startDate\": \"2017-09-05\", \"oldKeyValue\": {\"foo\": 1000, \"bar\": 2000, \"baz\": 3000}, \"anotherValue\": 0}';\n\nSET @new_col := JSON_INSERT(\n JSON_REMOVE(@my_col, '$.oldKeyValue'), \n '$.newKeyValue',\n JSON_EXTRACT(@my_col,'$.oldKeyValue')\n );\n\nSELECT @new_col;\n\n\nResult\n\n| @new_col |\n| ------------------------------------------------------------------------------------------------------------------------------- |\n| {\"endDate\": \"2018-10-10\", \"startDate\": \"2017-09-05\", \"newKeyValue\": {\"bar\": 2000, \"baz\": 3000, \"foo\": 1000}, \"anotherValue\": 0} |\n\n\n\n\nAs an alternative to Json_Extract(), we can also use -> operator to access the Value corresponding to a given Key in the JSON doc.\n\nUPDATE `my_table` \nSET `my_col` = JSON_INSERT(\n JSON_REMOVE(my_col, '$.oldKeyValue'), \n '$.newKeyValue', \n my_col->'$.oldKeyValue' \n );\n",

2018-11-22T14:56:00

MySQL Update or Rename a Key in JSON